#!/usr/bin/env python
# coding=utf-8
"""
@file: excel_util.py
@contact: bianzhiwei@iyoujia.com
@time   : 2019/4/28 16:32 
@Desc   :
"""
import os
import pandas as pd
from collections import OrderedDict
from report_system.utils import style_util
from report_system.utils import df_util
from report_system.utils.log_util import log
# 当前文件的路径
import xlrd

# import xlwt

pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")

pd.set_option('display.float_format', lambda x: '%.3f' % x)


def pd_to_excel(df_dict, file_name='未命名文件', engine='openpyxl', suffix='xlsx', na_rep='',
                inf_rep=0, index=True, merge_cells=True, freeze_panes=None):
    """
    将 df 转化为 excel
    :param df_dict: 字典   key 为  sheet_name 单元格名称  value 为 单元格的df
    :param file_name: 文件名
    :param engine:
    :param suffix: 后缀  xls 或者 xlsx
    :param inf_rep: 如果为null 或者 inf_rep
    :param na_rep: 如果为null   则替换成 na_rep
    :param freeze_panes: 冻结窗格
    :param merge_cells: 合并单元格
    :param index:
    :return:
    """
    path = father_path + '/report_files/'
    if not os.path.exists(path):
        os.makedirs(path)
    file_path = path + file_name + '.' + suffix
    with pd.ExcelWriter(file_path, engine=engine) as writer:
        for sheet_name in df_dict:
            log.info('正在生成 sheet: ' + str(sheet_name) + ' 的数据,请稍等。。。。。。')
            df = df_dict[sheet_name]

            df.to_excel(writer, sheet_name=sheet_name, na_rep=na_rep, inf_rep=inf_rep,
                        index=index, merge_cells=merge_cells, freeze_panes=freeze_panes)
            log.info('生成 sheet:【{}】 的 数据完成! '.format(sheet_name))
    return file_path


def to_excel_style(df_dict, file_name='未命名文件', engine='openpyxl', suffix='xlsx', na_rep='',
                   inf_rep=0, index=True, merge_cells=True, freeze_panes: tuple = None, uuid=None, colors_dict=None):
    """
    将 df 转化为 excel
    :param df_dict: 字典   key 为  sheet_name 单元格名称  value 为 单元格的df
    :param file_name: 文件名
    :param engine:
    :param suffix: 后缀  xls 或者 xlsx
    :param inf_rep: 如果为null 或者 inf_rep
    :param na_rep: 如果为null   则替换成 na_rep
    :param freeze_panes: freeze_panes=(3, 3)
    :param merge_cells: 合并单元格
    :param index:
    :param uuid:
    :param colors_dict:
    :return:
    """
    path = father_path + '/report_files/'
    if not os.path.exists(path):
        os.makedirs(path)
    file_path = path + file_name + '.' + suffix

    df_dict_total = OrderedDict()
    for sheet_name in df_dict:
        # 判断传过来的dataFrame 是否是 pd.DataFrame类型   也就是判断上style没
        if isinstance(df_dict[sheet_name], pd.DataFrame) and colors_dict is not None and sheet_name in colors_dict:
            df_dict_total[sheet_name] = style_util.style_color(df_dict[sheet_name], colors=colors_dict[sheet_name],
                                                               uuid=uuid)
        else:
            df_dict_total[sheet_name] = df_dict[sheet_name]

    with pd.ExcelWriter(file_path, engine=engine) as writer:
        for sheet_name in df_dict_total:
            log.info('正在生成 sheet: ' + str(sheet_name) + ' 的数据,请稍等。。。。。。')
            df = df_dict_total[sheet_name]
            df.to_excel(writer, sheet_name=sheet_name, na_rep=na_rep, inf_rep=inf_rep,
                        index=index, merge_cells=merge_cells, freeze_panes=freeze_panes)
            log.info('生成 sheet ' + str(sheet_name) + ' 的 数据完成')
    return file_path


def excel_to_df(file_path, header=1):
    """
    要转化的excel的绝对路径
    :param file_path:
    :param header: 取前几排为标题
    :return:
    """
    headers = [i for i in range(header)]
    excel_dict = dict()
    random_color_dict = dict()
    data = xlrd.open_workbook(file_path)
    for sheet in data.sheets():
        sheet_name = sheet.name
        df = pd.read_excel(file_path, sheet_name, header=headers)
        if header > 1:
            level1_key = df.keys().levels[0].to_list()
            colors_list = style_util.get_colors(len(level1_key))
            colors = dict(zip(level1_key, colors_list))
            random_color_dict[sheet_name] = colors
            # df = style_util.style_color(df, colors=colors)
        excel_dict[sheet_name] = df

    return excel_dict, random_color_dict


if __name__ == '__main__':
    print(excel_to_df('/Users/youjia/work/youjia_project/report_system/report_files/新版城市日报.xlsx', header=2))
